# -*- coding: utf8 -*-
import sqlite3
import Import


class ExamConfig:

    def __init__(self):
        pass

    @staticmethod
    def get_exam_types(f_parameters):
        path = Import.syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path+"DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        #намираме всички езици
        c.execute("""select id, trim(name), shif
                     from exam_types
                     order by trim(name)""")

        #създаваме резултата
        result = []
        while True:
            row = c.fetchone()
            if row is None:
                break
            result.append({'ID': row[0], 'NAME': row[1], 'SHIF': row[2]})
        return result

    @staticmethod
    def get_languages(f_parameters):
        path = Import.syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path+"DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        #намираме всички езици
        c.execute("""select id, trim(language), shif
                     from languages
                     where basic = 0
                     order by trim(language)""")

        #създаваме резултата
        result = []
        while True:
            row = c.fetchone()
            if row is None:
                break
            result.append({'ID': row[0], 'NAME': row[1], 'SHIF': row[2]})
        return result

    @staticmethod
    def get_lang_classifications(f_parameters):
        path = Import.syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path+'DB/EasyLangLearn_db.db')
        conn.text_factory = str
        c = conn.cursor()

        #намираме всички класификации
        c.execute("""select id, trim(name)
                     from classifications
                     where language_id = """+str(f_parameters['ID'])+"""
                     order by trim(name)""")

        #създаваме резултата
        result = []
        while True:
            row = c.fetchone()
            if row is None:
                break
            result.append({'ID': row[0], 'NAME': row[1]})
        return result

    @staticmethod
    def get_classification_info(f_parameters):
        path = Import.syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path+"DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        result = {}

        #намираме минимална дата
        c.execute("""select min(create_date)
                     from words
                     where classification_id = """+str(f_parameters['ID']))
        row = c.fetchone()
        result['MIN_DATE'] = row[0]

        #намираме максимална дата
        c.execute("""select max(create_date)
                     from words
                     where classification_id = """+str(f_parameters['ID']))
        row = c.fetchone()
        result['MAX_DATE'] = row[0]

        #намираме брой думи
        c.execute("""select count(id)
                     from words
                     where classification_id = """+str(f_parameters['ID']))
        row = c.fetchone()
        result['WORD_COUNT'] = row[0]

        #извеждаме списък с категориите

        c.execute("""select id, trim(name)
                     from categories
                     where classification_id = """+str(f_parameters['ID'])+"""
                     order by trim(name)""")

        #създаваме резултата
        result['CATEGORIES'] = []
        while True:
            row = c.fetchone()
            if row is None:
                break

            result['CATEGORIES'].append({'ID': row[0], 'NAME': row[1]})

        for cat in result['CATEGORIES']:
            c.execute("""select min(create_date)
                     from words w, words_to_categories wc
                     where w.id = wc.word_id and
                           wc.category_id = """+str(cat['ID']))
            row = c.fetchone()
            cat['MIN_DATE'] = row[0]

            c.execute("""select max(create_date)
                     from words w, words_to_categories wc
                     where w.id = wc.word_id and
                           wc.category_id = """+str(cat['ID']))
            row = c.fetchone()
            cat['MAX_DATE'] = row[0]

        return result

    @staticmethod
    def get_classification_log(f_parameters):
        path = Import.syspath
        if f_parameters['PATH']:
            path = f_parameters['PATH']

        conn = sqlite3.connect(path+"DB/EasyLangLearn_db.db")
        conn.text_factory = str
        c = conn.cursor()

        #извеждаме лог с изпити
        c.execute("""select exam_date, words_count, trim(exam_info), exam_percent
                     from exam_log
                     where classification_id = """+str(f_parameters['ID'])+"""
                     order by exam_date desc
                     limit 5""")

        #създаваме резултата
        result = []

        while True:
            row = c.fetchone()
            if row is None:
                break

            result.append({'EXAM_DATE': row[0], 'WORDS_COUNT': row[1], 'EXAM_INFO': row[2], 'EXAM_PERCENT': row[3]})

        return result

if __name__ == "__main__":
    res = ExamConfig.get_languages()
    print str(res)

    res = ExamConfig.get_lang_classifications({'ID': 3})
    print str(res)
    print str(len(res))

    res = ExamConfig.get_classification_info({'ID': 6})
    print str(res)

    res = ExamConfig.get_exam_types({'PATH': ''})
    print str(res)
